package com.my.lucky.repository;

import com.my.lucky.dto.CapitalFlowSummary;
import com.my.lucky.model.Stock;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.Optional;

@Repository
public interface StockRepository extends JpaRepository<Stock, Long> {

    // 根据股票代码查询
    List<Stock> findByCode(String code);

    // 根据日期范围查询
    List<Stock> findByTradeDateBetweenOrderByTradeDateDesc(LocalDate startDate, LocalDate endDate);

    // 根据股票代码和日期范围查询
    List<Stock> findByCodeAndCreatedAtBetweenOrderByTradeDateAsc(String code, LocalDateTime startDate, LocalDateTime endDate);

    // 根据股票代码和日期范围查询
    List<Stock> findByNameAndCreatedAtBetweenOrderByTradeDateAsc(String name, LocalDateTime startDate, LocalDateTime endDate);

    // 根据股票代码和日期范围查询
    List<Stock> findByCodeAndTradeDateBetweenOrderByTradeDateAsc(String code, LocalDate startDate, LocalDate endDate);

    // 查询最新的股票数据
    @Query("SELECT s FROM Stock s WHERE s.tradeDate = (SELECT MAX(s2.tradeDate) FROM Stock s2)")
    List<Stock> findLatestStocks();


    // 检查某个股票在某个日期是否存在数据
    boolean existsByCodeAndTradeDate(String code, LocalDate tradeDate);

    // 获取所有不同的交易日期，按日期降序排序
    @Query("SELECT DISTINCT s.tradeDate FROM Stock s ORDER BY s.tradeDate DESC")
    List<LocalDate> findDistinctTradeDateOrderByTradeDateDesc();

    // 根据日期范围查询，按代码升序和日期降序排序
    @Query(value = "SELECT * FROM stocks WHERE trade_date <= :endDate AND trade_date >= :startDate ORDER BY trade_date ASC", nativeQuery = true)
    List<Stock> findByTradeDateBetweenOrderByCodeAscTradeDateAsc(LocalDate startDate, LocalDate endDate);

    // 根据日期范围查询，按代码升序和日期降序排序
    @Query(value = "SELECT * FROM stocks WHERE trade_date <= :endDate AND trade_date >= :startDate AND code = :code ORDER BY trade_date ASC", nativeQuery = true)
    List<Stock> findByTradeDateBetweenOrderByCodeAsc(LocalDate startDate, LocalDate endDate,String code);


    @Query(value = "SELECT * FROM stocks " +
            "WHERE  trade_date <= :endDate AND trade_date >= :startDate " +
//            "AND FIND_IN_SET(:concep, concept) > 0 " + // 核心修改：精确匹配逗号分隔项
            " AND FIND_IN_SET(:concep, REPLACE(concept, ';', ',')) > 0 " +
//            "AND concept LIKE CONCAT('%', :concept, '%') " +
            "ORDER BY code ASC, trade_date DESC", // 按code升序、日期降序（匹配开头需求）
            nativeQuery = true)
    List<Stock> findByTradeDateBetweenAndCodeLikeOrderByConceptAscTradeDateDesc(LocalDate startDate, LocalDate endDate, String concep);


    // 根据日期范围查询，按代码升序和日期降序排序  涨停开板 或者涨幅度大于8%的
    @Query(value = "SELECT * FROM stocks WHERE trade_date <= :endDate AND trade_date >= :startDate AND (limitup_price=high_price or change_percent>=1)and high_price>=close_price ORDER BY trade_date ASC", nativeQuery = true)
    List<Stock> findByTradeDateBetweenLimitOrderByCodeAsc(LocalDate startDate, LocalDate endDate);

    // 查找重复的股票数据（相同代码和日期）
    @Query("SELECT s.code, s.tradeDate, COUNT(*) as count FROM Stock s GROUP BY s.code, s.tradeDate HAVING COUNT(*) > 1")
    List<Object[]> findDuplicateStocks();

    // 删除重复数据只保留ID最小的一条
    @Query(value = "DELETE FROM stock WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER(PARTITION BY code, trade_date ORDER BY id) AS row_num FROM stock) t WHERE t.row_num > 1)", nativeQuery = true)
    @org.springframework.transaction.annotation.Transactional
    void deleteDuplicateStocks();

    // 根据日期范围和代码前缀查询
    List<Stock> findByTradeDateBetweenAndCodeStartingWithOrderByCodeAscTradeDateAsc(LocalDate startDate, LocalDate endDate, String codePrefix);

    /**
     * 批量查询股票数据
     *
     * @param stockCodes 股票代码列表
     * @param startDate  开始日期
     * @param endDate    结束日期
     * @return 股票数据列表
     */
    @Query("SELECT s FROM Stock s WHERE s.code IN :stockCodes AND s.tradeDate BETWEEN :startDate AND :endDate ORDER BY s.code, s.tradeDate")
    List<Stock> findByCodesAndTradeDateBetween(@Param("stockCodes") List<String> stockCodes,
                                               @Param("startDate") LocalDate startDate,
                                               @Param("endDate") LocalDate endDate);

    /**
     * 使用JOIN查询优化性能
     */
    @Query("SELECT sqr, s1, s2, s3 FROM StockQueryRecord sqr " +
            "LEFT JOIN Stock s1 ON s1.code = SUBSTRING(sqr.stockCode, 4) AND s1.tradeDate = DATE(sqr.queryTime) " +
            "LEFT JOIN Stock s2 ON s2.code = SUBSTRING(sqr.stockCode, 4) AND s2.tradeDate = DATE(sqr.queryTime) + 1 " +
            "LEFT JOIN Stock s3 ON s3.code = SUBSTRING(sqr.stockCode, 4) AND s3.tradeDate = DATE(sqr.queryTime) + 2 " +
            "WHERE sqr.queryTime BETWEEN :startDateTime AND :endDateTime " +
            "AND (:type IS NULL OR sqr.type = :type)")
    List<Object[]> findStockAnalysisData(@Param("startDateTime") LocalDateTime startDateTime,
                                         @Param("endDateTime") LocalDateTime endDateTime,
                                         @Param("type") Integer type);

    /**
     * 查询指定股票从指定日期开始的前3条数据（处理交易日跳跃）
     * @param code      股票代码
     * @param startDate 开始日期
     * @return 最多3条股票数据，按日期升序排列
     */
    @Query(value = "SELECT * FROM stocks WHERE code = :code AND trade_date >= :startDate ORDER BY trade_date ASC LIMIT 3", nativeQuery = true)
    List<Stock> findTop3ByCodeAndTradeDateGreaterThanEqualOrderByTradeDateAsc(@Param("code") String code, @Param("startDate") LocalDate startDate);


    /**
     * 删除recordTime大于当天0点的涨停股票记录
     */
    @Modifying
    @Transactional
    @Query("DELETE FROM Stock s WHERE s.createdAt > CURRENT_DATE")
    void deleteByCreateTimeAfterToday();


    // 模糊查询股票代码
    Page<Stock> findByCodeContaining(String code, Pageable pageable);


    // 按日期范围查询资金流向数据
    @Query("SELECT new com.my.lucky.model.StockAggregation(" +
            "s.tradeDate, " +
            "SUM(s.capitalFlow), " +
            "SUM(s.mainCapitalFlow)) " +
            "FROM Stock s " +
            "WHERE s.tradeDate >= :startDate AND s.tradeDate <= :endDate " +
            "GROUP BY s.tradeDate " +
            "ORDER BY s.tradeDate DESC")
    List<Object[]> aggregateCapitalFlowByDateRange(
            @Param("startDate") LocalDate startDate,
            @Param("endDate") LocalDate endDate);

    // 查询最新的排名信息
    @Query("SELECT s FROM Stock s WHERE s.tradeDate = (SELECT MAX(tradeDate) FROM Stock) AND s.code = :code")
    Stock findMaxLatestStockByCode(@Param("code") String code);


    // 根据股票代码查询最新的股票数据
    @Query("SELECT s FROM Stock s WHERE (s.code = ?1 or s.name= ?1) AND s.tradeDate = (SELECT MAX(s2.tradeDate) FROM Stock s2 WHERE (s2.code = ?1 or s2.name = ?1))")
    Stock findLatestStockByCodeOrName(String code);

    /**
     * 按时间范围和股票代码分组统计主力资金流向
     *
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 返回股票代码和对应的主力资金流向总和
     */
    @Query("SELECT s.code, SUM(s.mainCapitalFlow) as totalMainFlow " +
            "FROM Stock s " +
            "WHERE s.tradeDate >= :startDate AND s.tradeDate <= :endDate " +
            "GROUP BY s.code " +
            "ORDER BY totalMainFlow DESC")
    List<Map<String, Object>> aggregateMainCapitalFlowByCodeAndDateRange(
            @Param("startDate") LocalDate startDate,
            @Param("endDate") LocalDate endDate);


    // 获取所有不同的股票代码
    @Query("SELECT DISTINCT s.code FROM Stock s")
    List<String> findAllDistinctCodes();

    // 按代码和日期范围查询股票数据，按rankPosition降序排列
    List<Stock> findByCreatedAtBetweenOrderByRankPositionAsc(LocalDateTime startDate, LocalDateTime endDate);


    // 然后修改查询方法
    @Query("SELECT new com.my.lucky.dto.CapitalFlowSummary(" +
            "s.code, " +
            "COALESCE(SUM(s.mainCapitalFlow), 0), " +
            "COALESCE(SUM(s.capitalFlow), 0) " +
            ") " +
            "FROM Stock s " +
            "WHERE s.tradeDate >= :startDate AND s.tradeDate <= :endDate " +
            "GROUP BY s.code")
    List<CapitalFlowSummary> sumCapitalFlowsByDateRange(
            @Param("startDate") LocalDate startDate,
            @Param("endDate") LocalDate endDate);


    // 获取最新的股票数据（用于获取最新排名）
    @Query(value = "SELECT * FROM stocks WHERE code = :code ORDER BY trade_date DESC LIMIT 1", nativeQuery = true)
    public Optional<Stock> findLatestByCode(@Param("code") String code);

} 